from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String(128))


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(128))
    hired_on = Column(DateTime, default=func.now())
    department_id = Column(Integer, ForeignKey('department.id'))
    department = relationship(
                    Department, backref=backref('employees',
                    uselist=True, cascade='delete,all'))


if __name__ == '__main__':
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from datetime import datetime

    engine = create_engine('sqlite:///example')
    session = sessionmaker()
    session.configure(bind=engine)
    s = session()

    # create tables
    Base.metadata.create_all(engine)

    # manipulate records

    # create
    d = Department(name="IT")
    emp1 = Employee(name="Alice", department=d)
    s.add(d)
    s.add(emp1)
    s.commit()

    # create
    emp2 = Employee(name="Bob", department_id=1)
    s.add(emp2)
    s.commit()

    # query
    s.query(Employee).all()

    # update
    emp1.hired_on = datetime.now()
    s.add(emp1)
    s.commit()

    # delete
    s.delete(emp1)  # delete one employee
    s.commit()
    s.delete(d)     # delete a whole department, cause all employees deleted
    s.commit()
